package com.slipper.codeless.resolve.support;

import com.slipper.codeless.resolve.ISqlBuilder;
import com.slipper.codeless.resolve.delete.Delete;
import com.slipper.codeless.resolve.query.*;
import com.slipper.codeless.resolve.update.Update;
import com.slipper.exception.CommonException;
import com.slipper.system.codeless.bo.DataFieldBO;
import com.slipper.util.Tool;
import com.slipper.util.UUID;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

public abstract class CommonSqlBuilder implements ISqlBuilder {
    public abstract String getSymbol();

    public String parseQueryField(String prefix, QueryField queryField) {
        if (Tool.isNotBlank(queryField.getTplContent())) {
            return queryField.getTplContent().replace("${value}", prefix + wrapSymbol(queryField.getOriginName()));
        } else if (queryField.getConcatName() == null || queryField.getConcatName().length == 0) {
            return prefix + wrapSymbol(queryField.getOriginName());
        } else {
            return "concat(" +
                    Arrays.stream(queryField.getConcatName())
                            .map(item -> prefix + wrapSymbol(item))
                            .collect(Collectors.joining(", '" + queryField.getConcatSymbol() + "', ")) +
                    ") as " + wrapSymbol(queryField.getOriginName());
        }
    }

    @Override
    public Map<String, Object> buildQueryMap(Query query) {
        if (Tool.isBlank(query.getTableName())) {
            throw new CommonException("数据集未配置数据表！");
        }

        Map<String, Object> resultMap = new HashMap<>();
        String fromBuilder;
        if (query.isLocal()) {
            fromBuilder = " from " + wrapSymbol(query.getTableName()) + " a ";
        } else {
            fromBuilder = " from (" + query.getTableName() + ") a ";
        }
        StringBuilder fieldBuilder = new StringBuilder();
        if (query.getQueryFieldList().isEmpty()) {
            fieldBuilder.append(", a.* ");
        } else {
            query.getQueryFieldList().forEach(field ->
                    fieldBuilder.append(", ").append(parseQueryField("a.", field)).append(" "));
        }

        StringBuilder whereBuilder = new StringBuilder();
        StringBuilder orderByBuilder = new StringBuilder();
        StringBuilder leftJoinBuilder = new StringBuilder();
        Map<String, String> nameMap = new HashMap<>();

        for (int i=0; i<query.getLeftJoinQueryList().size(); i++) {
            LeftJoinQuery ljQuery = query.getLeftJoinQueryList().get(i);
            String alias = ljQuery.getAlias();
            leftJoinBuilder.append(" left join ")
                    .append(ljQuery.getTargetTableName()) .append(" as ").append(alias)
                    .append(" on ")
                    .append(Tool.isBlank(ljQuery.getSourceAlias()) ? "a" : ljQuery.getSourceAlias()).append(".")
                    .append(wrapSymbol(ljQuery.getSourceFieldName())).append("=")
                    .append(alias).append(".").append(wrapSymbol(ljQuery.getTargetPrimaryKey())).append(" ");

            ljQuery.getTargetFieldMap().forEach((key, value) -> {
                fieldBuilder.append(", ").append(alias).append(".").append(wrapSymbol(key))
                        .append(" as ").append(wrapSymbol(value));
                nameMap.put(value, alias + "." + wrapSymbol(key));
            });

            String fieldPrefixOn = "lj_on_" + alias + '_';
            StringBuilder itemBuilder = new StringBuilder();
            resolveCondition(alias, fieldPrefixOn, itemBuilder, resultMap, " and ", ljQuery.getOnConditionList());
            if (!itemBuilder.isEmpty()) {
                leftJoinBuilder.append(" and ").append(itemBuilder.substring(4));
            }

            String fieldPrefixAnd = "lj_and_" + alias + '_';
            itemBuilder = new StringBuilder();
            resolveCondition(alias, fieldPrefixAnd, itemBuilder, resultMap, " and ", ljQuery.getConditionList());
            if (!itemBuilder.isEmpty()) {
                whereBuilder.append(" and ").append(itemBuilder.substring(4));
            }

            String fieldPrefixOr = "lj_or_" + alias + '_';
            itemBuilder = new StringBuilder();
            resolveCondition(alias, fieldPrefixOr, itemBuilder, resultMap, " or ", ljQuery.getOrConditionList());
            if (!itemBuilder.isEmpty()) {
                whereBuilder.append(" and ").append(itemBuilder.substring(4));
            }

            if (Tool.isNotBlank(ljQuery.getSortField())) {
                orderByBuilder.append(", ")
                        .append(alias).append(".")
                        .append(wrapSymbol(ljQuery.getSortField())).append(" ")
                        .append(ljQuery.getSortType()).append(" ");
            }
        }

        query.getConditionList().forEach(conditionList -> {
            StringBuilder itemBuilder = new StringBuilder();
            resolveCondition("a", "fm_", itemBuilder, resultMap, " and ", conditionList);
            if (!itemBuilder.isEmpty()) {
                whereBuilder.append(" and (").append(trimConnector(itemBuilder.toString())).append(")");
            }
        });

        StringBuilder whereOrBuilder = new StringBuilder();
        for (List<Condition> conditions : query.getOrConditionList()) {
            StringBuilder orBuilder = new StringBuilder();
            resolveCondition("a", "fm_or_", orBuilder, resultMap, " or ", conditions, nameMap);
            if (!orBuilder.isEmpty()) {
                whereOrBuilder.append(" and (").append(trimConnector(orBuilder.toString())).append(")");
            }
        }

        query.getSortFieldList().stream()
                .sorted(Comparator.comparingInt(SortField::getOrdinal))
                .forEach(item ->
                        orderByBuilder.append(", ")
                                .append(Tool.isBlank(item.getTableAlias()) ? "a" : wrapSymbol(item.getTableAlias()))
                                .append(".")
                                .append(wrapSymbol(item.getFieldName())).append(" ")
                                .append(item.getOrdinalMode()));

        String sql = " select " + fieldBuilder.substring(1) + fromBuilder + leftJoinBuilder;
        String where = "";
        if (!whereBuilder.isEmpty()) {
            where = " where " + whereBuilder.substring(4);
        }
        if (!whereOrBuilder.isEmpty()) {
            if (!where.isEmpty()) {
                where += whereOrBuilder;
            } else {
                where = " where " + whereOrBuilder.substring(4);
            }
        }
        sql += where;
        if (!orderByBuilder.isEmpty()) {
            sql += " order by " + orderByBuilder.substring(2);
        }
        resultMap.put("sql", sql);
        return resultMap;
    }

    private void resolveCondition(
            String tableAlias,
            String fieldPrefix,
            StringBuilder builder,
            Map<String, Object> map,
            String conditionType,
            List<Condition> conditionList) {
        StringBuilder tmpBuilder = new StringBuilder();
        resolveCondition(tableAlias, fieldPrefix, tmpBuilder, map, conditionType, conditionList, null);
        builder.append(clearInValid(tmpBuilder.toString()));
    }

    private void resolveCondition(
            String tableAlias,
            String fieldPrefix,
            StringBuilder builder,
            Map<String, Object> map,
            String conditionType,
            List<Condition> conditionList,
            Map<String, String> nameMap) {
        if (conditionList.size() > 1) {
            conditionList.sort(Comparator.comparingInt(Condition::getOrdinal));
        }
        for (int ci=0; ci<conditionList.size(); ci++) {
            Condition condition = conditionList.get(ci);

            if (condition.getItemList().isEmpty()) {
                break;
            }
            String queryField;
            if (condition.isAlias() && nameMap != null && nameMap.containsKey(condition.getFieldName())) {
                queryField = nameMap.get(condition.getFieldName());
            } else {
                queryField = " "
                        + (Tool.isBlank(condition.getAliasName()) ? tableAlias : condition.getAliasName())
                        + "." + wrapSymbol(condition.getFieldName());
            }
            StringBuilder fieldWhere = new StringBuilder();
            for (int i = 0; i < condition.getItemList().size(); i++) {
                String param = fieldPrefix + condition.getFieldName() + UUID.getUUID();
                ConditionItem item = condition.getItemList().get(i);
                if (Tool.isBlank(item.getMatchMode())) {
                    item.setMatchMode(Condition.MATCH_MODE_LIKE);
                }

                if (Tool.isNotBlank(item.getMatchValue())) {
                    resolveConditionItem(queryField, condition.getFieldType(), param, fieldWhere, map, item);
                }
            }

            if (!fieldWhere.isEmpty()) {
                if (builder.isEmpty()) {
                    builder.append(conditionType);
                }
                if (ci > 0 && Tool.isNotBlank(condition.getConnectSymbol())) {
                    builder.append(" ").append(condition.getConnectSymbol()).append(" ");
                }
                if (Tool.isNotBlank(condition.getFrontBracket())) {
                    builder.append(condition.getFrontBracket());
                }
                if (condition.getItemList().size() > 1) {
                    builder.append("(").append(fieldWhere.substring(3)).append(")");
                } else {
                    builder.append(fieldWhere.substring(3));
                }
                if (Tool.isNotBlank(condition.getBehindBracket())) {
                    builder.append(condition.getBehindBracket());
                }
            } else {
                int frontBracket = 0;
                int behindBracket = 0;
                if (Tool.isNotBlank(condition.getFrontBracket())) {
                    frontBracket = condition.getFrontBracket().trim().length();
                }
                if (Tool.isNotBlank(condition.getBehindBracket())) {
                    behindBracket = condition.getBehindBracket().trim().length();
                }

                if (Math.abs(frontBracket - behindBracket) > 0) {
                    if (builder.isEmpty()) {
                        builder.append(conditionType);
                    }

                    if (ci > 0 && Tool.isNotBlank(condition.getConnectSymbol())) {
                        builder.append(" ").append(condition.getConnectSymbol()).append(" ");
                    }
                    builder.append(Tool.fillLength(
                            "",
                            Math.abs(frontBracket - behindBracket),
                            frontBracket > behindBracket ? '(' : ')')
                    );
                }
            }
        }
    }

    private void resolveConditionItem(
            String fieldName,
            String fieldType,
            String param,
            StringBuilder builder,
            Map<String, Object> map,
            ConditionItem condition) {
        switch (condition.getMatchMode()) {
            case Condition.MATCH_MODE_LIKE:
                builder.append(" or ");
                builder.append(fieldName);
                builder.append(" like ").append("#{").append(param).append("} ");
                map.put(param, "%" + String.valueOf(condition.getMatchValue()).trim() + "%");
                break;
            case Condition.MATCH_MODE_LT:
                builder.append(" or ");
                builder.append(fieldName);
                builder.append(" < ").append("#{").append(param).append("} ");
                map.put(param, convertValue(fieldType, condition.getMatchValue()));
                break;
            case Condition.MATCH_MODE_LTE:
                builder.append(" or ");
                builder.append(fieldName);
                builder.append(" <= ").append("#{").append(param).append("} ");
                map.put(param, convertValue(fieldType, condition.getMatchValue()));
                break;
            case Condition.MATCH_MODE_EQU:
                builder.append(" or ");
                builder.append(fieldName);
                if ("字段".equals(fieldType)) {
                    builder.append(" = ").append("a.").append(wrapSymbol((String) condition.getMatchValue()));
                } else {
                    builder.append(" = ").append("#{").append(param).append("} ");
                    map.put(param, convertValue(fieldType, condition.getMatchValue()));
                }
                break;
            case Condition.MATCH_MODE_GT:
                builder.append(" or ");
                builder.append(fieldName);
                builder.append(" > ").append("#{").append(param).append("} ");
                map.put(param, convertValue(fieldType, condition.getMatchValue()));
                break;
            case Condition.MATCH_MODE_GTE:
                builder.append(" or ");
                builder.append(fieldName);
                builder.append(" >= ").append("#{").append(param).append("} ");
                map.put(param, convertValue(fieldType, condition.getMatchValue()));
                break;
            case Condition.MATCH_MODE_NO_EQU:
                builder.append(" or ");
                builder.append(fieldName);
                builder.append(" != ").append("#{").append(param).append("} ");
                map.put(param, condition.getMatchValue());
                break;
            case Condition.MATCH_MODE_IN:
                builder.append(" or ");
                builder.append(fieldName);
                builder.append(" in (");
                String[] matchValue = condition.getMatchValue().toString().split(",");
                for (int i=0; i<matchValue.length; i++) {
                    if (i > 0) {
                        builder.append(", ");
                    }
                    String tempParam = param + "_" + i;
                    builder.append("#{").append(tempParam).append("}");
                    map.put(tempParam, convertValue(fieldType, matchValue[i].trim()));
                }
                builder.append(") ");
                break;
            case Condition.MATCH_MODE_BTN:
                builder.append(" or ");
                String[] between = condition.getMatchValue().toString().split(",");
                if (between.length == 2) {
                    builder.append(fieldName)
                            .append(" between #{").append(param).append("_1} ")
                            .append(" and #{").append(param).append("_2} ");
                    map.put(param + "_1", convertValue(fieldType, between[0]));
                    map.put(param + "_2", convertValue(fieldType, between[1]));
                }
                break;
            case Condition.MATCH_MODE_RG:
                builder.append(" or ");
                String[] rg = condition.getMatchValue().toString().split(",");
                if (rg.length == 2) {
                    builder.append(fieldName).append(" > #{").append(param).append("_1} " );
                    builder.append(" and ");
                    builder.append(fieldName).append(" < #{").append(param).append("_2} ");
                    map.put(param + "_1", convertValue(fieldType, rg[0]));
                    map.put(param + "_2", convertValue(fieldType, rg[1]));
                }
                break;
            case Condition.MATCH_MODE_RGE:
                builder.append(" or ");
                String[] rge = condition.getMatchValue().toString().split(",");
                if (rge.length == 2) {
                    builder.append(fieldName).append(" >= #{").append(param).append("_1} " );
                    builder.append(" and ");
                    builder.append(fieldName).append(" <= #{").append(param).append("_2} ");
                    map.put(param + "_1", convertValue(fieldType, rge[0]));
                    map.put(param + "_2", convertValue(fieldType, rge[1]));
                }
                break;
            case Condition.MATCH_MODE_RECURSIVE:
                builder.append(" or ")
                        .append(fieldName)
                        .append(" in (")
                        .append(getQueryRecursiveSql(
                                condition.getTableName(),
                                condition.getPidName(),
                                condition.getIdName(),
                                param))
                        .append(")");
                map.put(param, condition.getMatchValue());
                break;
            case Condition.MATCH_MODE_ORG_LEVEL:
                builder.append(" or ")
                        .append(fieldName)
                        .append(" in (")
                        .append(getQueryOrgLevelSql(param))
                        .append(")");
                map.put(param, condition.getMatchValue());
                break;
            case Condition.MATCH_MODE_NOT_ADMIN:
                builder.append(" or ")
                        .append(fieldName)
                        .append(" not in (")
                        .append(getQueryAdminEmployeeSql())
                        .append(")");
                break;
        }
    }

    private Object convertValue(String fieldType, Object value) {
        if (Tool.isBlank(fieldType) || Tool.isBlank(value)) return value;

        if (DataFieldBO.FIELD_TYPE_INTEGER.equals(fieldType)
                && !(value instanceof Integer)
                && !(value instanceof Long)) {
            return Long.valueOf(value.toString());
        } else {
            return value;
        }
    }

    private String clearInValid(String sql) {
        return clearInValid(sql, 100);
    }

    private String clearInValid(String sql, int times) {
        if (Tool.isBlank(sql)) return "";

        boolean clear = false;
        Map<String, String> patterMap = new LinkedHashMap<>(){{
            put("\\([ ]?+and[ ]?+\\)", "");
            put("\\([ ]?+or[ ]?+\\)", "");
            put("\\([ ]?+and[ ]?+", "(");
            put("\\([ ]?+or[ ]?+", "(");
            put("[ ]?+and[ ]?+\\)", ")");
            put("[ ]?+or[ ]?+\\)", ")");
            put("\\([ ]?+\\)", "");
            put("(?:\\s+and)+\\s+and\\s*", " and ");
            put("\\s+and\\s*$", "");
            put("(?:\\s+or)+\\s+or\\s*", " or ");
            put("\\s+or\\s*$", "");
        }};
        for (Map.Entry<String, String> entry : patterMap.entrySet()) {
            Matcher matcher = Pattern.compile(entry.getKey()).matcher(sql);
            StringBuilder sb = new StringBuilder();
            while (matcher.find()) {
                clear = true;
                matcher.appendReplacement(sb, entry.getValue());
            }
            matcher.appendTail(sb);
            sql = sb.toString();
        }

        return clear && times > 0 ? clearInValid(sql, --times) : sql;
    }

    public abstract String getQueryRecursiveSql(String tableName, String pidName, String idName, String param);

    public abstract String getQueryOrgLevelSql(String orgId);

    public String getQueryAdminEmployeeSql() {
        return "select er.employee_id " +
                " from sys_employee_role er, sys_role r " +
                " where er.role_id = r.id and r.admin='1' ";
    }

    @Override
    public Map<String, Object> buildUpdateMap(Update update) {
        if (Tool.isBlank(update.getTableName())) {
            throw new CommonException("数据集未配置数据表！");
        }

        Map<String, Object> resultMap = new HashMap<>();
        if (update.isInsert()) {
            if (Tool.isNotBlank(update.getPkIdName()) && Tool.isBlank(update.getPkIdValue())) {
                update.setPkIdValue(UUID.getUUID());
            }

            List<String> fieldList = new ArrayList<>();
            List<String> valueList = new ArrayList<>();

            if (Tool.isNotBlank(update.getPkIdName())) {
                fieldList.add(wrapSymbol(update.getPkIdName()));
                valueList.add("#{" + update.getPkIdName() + "}");
                resultMap.put(update.getPkIdName(), update.getPkIdValue());
            }

            update.getUpdateFieldList().forEach(field -> {
                fieldList.add(wrapSymbol(field.getName()));
                valueList.add("#{" + field.getName() + "}");
                resultMap.put(field.getName(), field.getValue());
            });
            if (Tool.isBlank(fieldList)) {
                throw new CommonException("提交无效的更新数据！");
            }

            String sql = "insert into " + wrapSymbol(update.getTableName())
                    + "(" + String.join(", ", fieldList) + ") values (" + String.join(", ", valueList) + ")";
            resultMap.put("sql", sql);
        } else {
            List<String> setList = new ArrayList<>();
            update.getUpdateFieldList().forEach(field -> {
                setList.add(wrapSymbol(field.getName()) + "=#{" + field.getName() + "}");
                resultMap.put(field.getName(), field.getValue());
            });

            if (setList.isEmpty()) {
                throw new CommonException("提交无效的更新数据！");
            }

            String sql = "update " + wrapSymbol(update.getTableName())
                    + " set " + String.join(", ", setList)
                    + " where " + wrapSymbol(update.getPkIdName()) + "=#{" + update.getPkIdName() + "}";
            resultMap.put(update.getPkIdName(), update.getPkIdValue());
            resultMap.put("sql", sql);
        }
        return resultMap;
    }

    @Override
    public Map<String, Object> buildDeleteMap(Delete delete) {
        if (delete.isCleanTable()) {
            Map<String, Object> resultMap = new HashMap<>();
            String sql = "delete from "  + wrapSymbol(delete.getTableName());
            resultMap.put("sql", sql);
            return resultMap;
        }

        if (Tool.isBlank(delete.getTableName())) {
            throw new CommonException("数据集未配置数据表！");
        }
        if (Tool.isBlank(delete.getPkIdName())) {
            throw new CommonException("数据集未配置主键字段！");
        }
        if (delete.getPkIdList().isEmpty()) {
            throw new CommonException("提交无效的删除数据！");
        }

        Map<String, Object> resultMap = new HashMap<>();
        StringBuilder fields = new StringBuilder();
        delete.getPkIdList().forEach(pkId -> {
            String param = "pkId_" + pkId.replaceAll("\\.", "_");
            fields.append(", ").append("#{").append(param).append("} ");
            resultMap.put(param, pkId);
        });
        fields.deleteCharAt(0);

        String sql = "delete from " + wrapSymbol(delete.getTableName())
                + " where " + wrapSymbol(delete.getPkIdName()) + " in (" + fields + ")";
        resultMap.put("sql", sql);
        return resultMap;
    }

    public String wrapSymbol(String value) {
        String symbol = getSymbol();
        return symbol + value + symbol;
    }

    public String trimConnector(String value) {
        if (Tool.isBlank(value)) return "";

        boolean change = false;
        value = value.trim();
        if (value.startsWith("and")) {
            value = value.substring("and".length()).trim();
            change = true;
        }
        if (value.startsWith("or")) {
            value = value.substring("or".length()).trim();
            change = true;
        }
        if (value.startsWith("(") && value.endsWith(")")) {
            value = value.substring(1, value.length() - 1);
            change = true;
        }
        return change ? trimConnector(value) : value;
    }
}
